var mysql = require('mysql');

function DBHelper(){
}

DBHelper.prototype.connectDB = function(){
	this.connection = mysql.createConnection({
		host : 'localhost',
		user : 'root',
		password : 'GhostRain@2677',
		port : '3306',
		database : 'otaku',
		connectionLimit: 5,
	});

	this.connection.connect( err =>{
		if(err){
			console.log('error when connecting to db:', err);
			setTimeout(this.connectDB, 2000);
		}
		console.log("connect db success");
	});

	this.connection.on('error',err => {
		console.log('db error', err);
		if (err.code === 'PROTOCOL_CONNECTION_LOST') {
			this.connectDB();
		}
		else{
			throw err;
		}
	});
}

//查询新番索引
DBHelper.prototype.getAllSeasonInfo = function(callback)
{
	var sql = 'SELECT * FROM season;';
	this.connection.query(sql,function (err, result) {
		if(err){
			console.log('[SELECT ERROR] - ',err.message);
		}
		else{
			if (callback) {
				callback(result);
			}
		}
	});
}

//查询用户,并返回UnionId
DBHelper.prototype.getUserInfo = function (obj,callback)
{
	var sql = "SELECT * FROM wxuser WHERE user_id = '" + obj.unionId + "';";
	this.connection.query(sql, function (err, result){
		if (err) {
			console.log('[SELECT ERROR] - ',err.message);
		}
		else {
			if(callback){
				callback(result);
			}
		}
	});
}

//新建用户
DBHelper.prototype.createUser = function (userObj,callback){
	var createSQL = "INSERT INTO wxuser (user_id, openId, nickName, gender, city, province, country) VALUES ('"+userObj.unionId+"','"+userObj.openId+"','"+userObj.nickName+"','"+userObj.gender+"','"+userObj.city+"','"+userObj.province+"','"+userObj.country+"');";
	this.connection.query(createSQL,function(err, result){
		if(err){
			console.log('[INSERT ERROR] - ',err.message);
		}
		else{
			console.log('[INSERT unionId] - ',userObj.unionId);
			if(callback){
				callback(result);
			}
		}
	});
}

//获取用户全部收藏
DBHelper.prototype.getUserAllFollowInfo = function (userObj,callback){
	var sql = "SELECT season_id,follow_type FROM user_season WHERE user_id = '" + userObj.unionId + "';";
	console.log(sql);
	this.connection.query(sql,function(err, result){
		if(err){
			console.log('[SELECT ERROR] - ',err.message);
		}
		else{
			console.log(result);
			if(callback){
				callback(result);
			}
		}
	});
}

//插入一条收藏信息
DBHelper.prototype.insertUserFollowInfo = function (obj,callback){
	var sql = "INSERT INTO user_season (user_id,season_id,follow_type) VALUES ('"+obj.unionId+"','"+obj.seasonId+"','"+obj.followType+"');"
	this.connection.query(sql,function(err,result){
		var sendObj = {};
		if(err){
			console.log('[INSERT ERROR] - ',err.message);
		}
		else{
			if(callback){
				callback(result);
			}
		}
	});
}

//更新收藏标记
DBHelper.prototype.updateUserFollow = function (obj,callback){
	var updateSQL = "UPDATE user_season SET follow_type = '"+obj.followType+"' WHERE user_id = '"+obj.unionId+"' AND season_id = '"+obj.seasonId+"';";
	this.connection.query(updateSQL,function(err,result){
		var sendObj = {};
		if(err){
			console.log('[UPDATE ERROR] - ',err.message);
		}
		else{
			if(callback){
				callback(result);
			}
		}
	});
}

//设置用户单条收藏信息
DBHelper.prototype.setUserFollowInfo = function (obj,callback){
	var sql = "SELECT user_id,season_id,follow_type FROM user_season WHERE user_id = '"+obj.unionId+"' AND season_id = '"+obj.seasonId+"';"
	this.connection.query(sql, (err, result) => {
		if(err){
			console.log('[SELECT ERROR] - ',err.message);
		}
		else{
			//没查到
			if (result == false) {
				this.insertUserFollowInfo(obj,callback);
			}
			else{
				//如果有直接更新表
				this.updateUserFollow(obj,callback);
			}
		}
	});
}

module.exports = DBHelper